﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace SystemCustomers.DBUtils
{
    public class SingletonManagerDb
    {
        #region Singelton

        private static SingletonManagerDb instance;

        public static SingletonManagerDb Instance
        {
            get { return instance ?? (instance = new SingletonManagerDb()); }
        }

        private SingletonManagerDb()
        {
            // this will happens only once
            // Do your connection stuff in here
            connection = new SqlConnection(Manage.ConnectionString);
            connection.Open();
        }

        public static void InstantIsNull()
        {
            instance = null;
        }
        
        #endregion Singelton

        #region Members

        private SqlConnection connection;
        private const string Titel = "Data Base Error";

        #endregion Members


        #region Manage Company
        /// <summary>
        /// 
        ///
        /// </summary>
        public void DbInsertCompany(string privateName, string address, string companyName,
            string phone, string mobile, string city, string companyNumber,
            string fax, string email, string zipNum, string postalNum, string paymentTerms)
        {
            try
            {
                using (var command = new SqlCommand("INSERT INTO Companys VALUES(@companyName, @contactName, @mobilePhone, @email, @Phone, @FAX, @companynumber, @address, @city, @ZIP, @PostalNum, @paymentTerms)", this.connection))
                {
                    command.Parameters.Add(new SqlParameter("companyName", companyName));
                    command.Parameters.Add(new SqlParameter("contactName", privateName));
                    command.Parameters.Add(new SqlParameter("mobilePhone", mobile));
                    command.Parameters.Add(new SqlParameter("email", email));
                    command.Parameters.Add(new SqlParameter("Phone", phone));
                    command.Parameters.Add(new SqlParameter("FAX", fax));
                    command.Parameters.Add(new SqlParameter("companynumber", companyNumber));
                    command.Parameters.Add(new SqlParameter("address", address));
                    command.Parameters.Add(new SqlParameter("city", city));
                    command.Parameters.Add(new SqlParameter("ZIP", zipNum));
                    command.Parameters.Add(new SqlParameter("PostalNum", postalNum));
                    command.Parameters.Add(new SqlParameter("paymentTerms", paymentTerms));
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return;
            }
        }


        public DataTable DbViewingCompany()
        {
            using (var dtCustomers = new DataTable())
            {
                try
                {
                    using (var command = new SqlCommand("SELECT * FROM Companys", this.connection))
                    {
                        dtCustomers.Load(command.ExecuteReader(CommandBehavior.Default));
                    }
                }
                catch (Exception ex)
                {
                    new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                    MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                    MessageUtils.LogUtils.WriteToLog(ex.Message);
                    return null;
                }
                return dtCustomers;
            }
        }


        public DataTable DbViewingCustomersIdAndName()
        {
            using (var dtCustomers = new DataTable())
            {
                try
                {
                    using (var command = new SqlCommand("SELECT idCompany,companyName FROM Companys", this.connection))
                    {
                        dtCustomers.Load(command.ExecuteReader(CommandBehavior.Default));
                    }
                }
                catch (Exception ex)
                {
                    new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                    MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                    MessageUtils.LogUtils.WriteToLog(ex.Message);
                    return null;
                }
                return dtCustomers;
            }
        }



        public void DeleteRowCustomer(string id)
        {
            string queryDeleteCustomer = String.Format("DELETE from Companys WHERE idCompany ={0};", id);
            try
            {
                SqlCommand command;
                using (command = new SqlCommand(queryDeleteCustomer, this.connection))
                {
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return;
            }
        }



        public void DbUpdateCustomer(string id, string privateName, string address, string companyName,
           string phone, string mobile, string city, string Fax, string email,
            string zipNum, string postalNum, string paymentTerms)
        {
            try
            {
                string query =
                    "UPDATE Companys SET companyName=@companyName,contactName=@contactName,mobilePhone=@mobilePhone,email=@email,Phone=@Phone,FAX=@FAX,address=@address,city=@city,ZIP=@ZIP,PostalNum=@PostalNum,paymentTerms=@paymentTerms WHERE idCompany=" +
                    id;

                using (var command = new SqlCommand(query, this.connection))
                {
                    command.Parameters.AddWithValue("@contactName", privateName);
                    command.Parameters.AddWithValue("@address", address);
                    command.Parameters.AddWithValue("@companyName", companyName);
                    command.Parameters.AddWithValue("@Phone", phone);
                    command.Parameters.AddWithValue("@mobilePhone", mobile);
                    command.Parameters.AddWithValue("@city", city);
                    command.Parameters.AddWithValue("@FAX", Fax);
                    command.Parameters.AddWithValue("@email", email);
                    command.Parameters.AddWithValue("@ZIP", zipNum);
                    command.Parameters.AddWithValue("@PostalNum", postalNum);
                    command.Parameters.AddWithValue("@paymentTerms", paymentTerms);
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return;
            }
        }

        #endregion Manage Company


        #region Manage Service
        /// Start Db Manage Type Of Services
        /// <summary>
        /// 
        /// </summary>
        /// <param name="serviceName"></param>

        public void DbInsertService(string serviceName)
        {
            try
            {
                using (
                    var command = new SqlCommand("INSERT INTO Services VALUES(@servicesName)", this.connection))
                {
                    command.Parameters.Add(new SqlParameter("servicesName", serviceName));
                    command.ExecuteNonQuery();
                }
            }
            
                  catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return;
            }
        }

        public DataTable DbViewingServices()
        {
            using(var dtServices = new DataTable())
            {
                try
                {
                    using (var command = new SqlCommand("SELECT * FROM Services", this.connection))
                    {
                        dtServices.Load(command.ExecuteReader(CommandBehavior.Default));
                    }
                }
                catch (Exception ex)
                {
                    new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                    MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                    MessageUtils.LogUtils.WriteToLog(ex.Message);
                    return null;
                }
                return dtServices;
            }
        }



        public void DbUpdateServices(string id, string serviceName)
        {
            try
            {
                string query = "UPDATE Services SET servicesName=@servicesName WHERE idService=" + id;
                using (var command = new SqlCommand(query, this.connection))
                {
                    command.Parameters.AddWithValue("@servicesName", serviceName);
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return;
            }
        }


        public void DeleteServiceRow(string id)
        {
            string query = String.Format("DELETE from Services WHERE idService={0};", id);
            try
            {
                using (var command = new SqlCommand(query, this.connection))
                {
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return;
            }
        }

        #endregion Manage Service


        #region Manage Service To Customers

        public DataTable DbViewingNameCustandIdCust()
        {
            using (var dtNameandId = new DataTable())
            {
                try
                {
                    using (var command = new SqlCommand("SELECT companyName,idCompany FROM Companys", this.connection))
                    {
                        dtNameandId.Load(command.ExecuteReader(CommandBehavior.Default));
                    }
                }
                catch (Exception ex)
                {
                    new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                    MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                    MessageUtils.LogUtils.WriteToLog(ex.Message);
                }
                return dtNameandId;
            }
        }

        public DataTable DbViewServiceNameandIdCust()
        {
            using (var dtServiceandId = new DataTable())
            {
                try
                {
                    using (var command = new SqlCommand("SELECT servicesName,idService FROM Services", this.connection))
                    {
                        dtServiceandId.Load(command.ExecuteReader(CommandBehavior.Default));
                    }
                }
                catch (Exception ex)
                {
                    new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                    MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                    MessageUtils.LogUtils.WriteToLog(ex.Message);
                }
                return dtServiceandId;
            }
        }


        public void DbInsertManageCust(string companyId, string serviceId, DateTime startDate, DateTime endDate, string price, string priceCost, string companyName, string serviceName,bool paid)
        {
            try
            {
                using (
                    var command =
                        new SqlCommand(
                            "INSERT INTO ServicesCompany VALUES(@idCompany,@idService,@startdate,@enddate,@price,@priceCost,@paid)",
                            this.connection))
                {
                    if (priceCost == string.Empty) priceCost = "0";
                    command.Parameters.Add(new SqlParameter("idCompany", companyId));
                    command.Parameters.Add(new SqlParameter("idService", serviceId));
                    command.Parameters.Add(new SqlParameter("startdate", startDate));
                    command.Parameters.Add(new SqlParameter("enddate", endDate));
                    command.Parameters.Add(new SqlParameter("price", price));
                    command.Parameters.Add(new SqlParameter("priceCost", priceCost));
                    command.Parameters.Add(new SqlParameter("paid", paid));
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
            }
        }


        public DataTable DbViewingServicesToCust()
        {
            using (var dtServices = new DataTable())
            {
                string query = "SELECT SC.idService, SC.idCompany, C.companyName,S.servicesName,SC.startdate,SC.enddate,SC.price,SC.priceCost,SC.paid FROM ServicesCompany SC LEFT JOIN Companys C ON SC.idCompany = C.idCompany LEFT JOIN Services S ON SC.idService = S.idService ORDER BY C.companyName";
                try
                {
                    using (var command = new SqlCommand(query, this.connection))
                    {
                        dtServices.Load(command.ExecuteReader(CommandBehavior.Default));
                    }
                }
                catch (Exception ex)
                {
                    new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                    MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                    MessageUtils.LogUtils.WriteToLog(ex.Message);
                }
                return dtServices;
            }
        }


        public void DeleteRowServiceToCompany(string companyId, string serviceId)
        {
            string query = String.Format("DELETE from ServicesCompany WHERE idCompany={0}and idService={1};", companyId,
                                         serviceId);
            try
            {
                using (var command = new SqlCommand(query, this.connection))
                {
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
            }
        }

        public void DbUpdateServiceToCustomer(DateTime startDate, DateTime endDate, string price, string priceCost, string customerId, string serviceId,bool paid)
        {
            try
            {
                string query =
                    String.Format(
                        "UPDATE ServicesCompany SET startdate=@startdate,enddate=@enddate,price=@price,priceCost=@priceCost,paid=@paid WHERE idCompany={0}and idService={1};",
                        customerId, serviceId);
                using (var command = new SqlCommand(query, this.connection))
                {
                    command.Parameters.AddWithValue("@startdate", startDate);
                    command.Parameters.AddWithValue("@enddate", endDate);
                    command.Parameters.AddWithValue("@price", price);
                    command.Parameters.AddWithValue("@priceCost", priceCost);
                    command.Parameters.AddWithValue("@paid", paid);
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
            }
        }

        #endregion Manage Service To Customers


        #region Manage Login And Users

        public string DbLogin(string userName, string password)
        {
            string vPermission = string.Empty;
            try
            {
                using (
                    var command =
                        new SqlCommand(
                            "SELECT VPermission FROM Login WHERE UserName = @UserName and Password = @Password",
                            this.connection))
                {
                    command.Parameters.Add(new SqlParameter("@UserName", userName));
                    command.Parameters.Add(new SqlParameter("@Password", password));

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader != null)
                            while (reader.Read())
                            {
                                vPermission = Convert.ToString(reader["VPermission"]);
                            }
                    }
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
            }
            return vPermission;
        }

        public bool DbGetUserName(string userName)
        {
            string userNameFromTable = string.Empty;
            try
            {
                using (var command = new SqlCommand("SELECT * FROM Login WHERE UserName = @UserName ", this.connection))
                {
                    command.Parameters.Add(new SqlParameter("@UserName", userName));

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        if (reader != null)
                            while (reader.Read())
                            {
                                userNameFromTable = Convert.ToString(reader["UserName"]);
                                if (userNameFromTable == userName)
                                    return false;
                            }
                    }
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
            }
            return true;
        }


        public void DbAddUser(string userName, string password, string firstName, string permission)
        {
            try
            {
                using (
                    var command =
                        new SqlCommand("INSERT INTO Login VALUES(@UserName,@Password,@FirstName,@VPermission)", this.connection))
                {
                    command.Parameters.Add(new SqlParameter("UserName", userName));
                    command.Parameters.Add(new SqlParameter("Password", password));
                    command.Parameters.Add(new SqlParameter("FirstName", firstName));
                    command.Parameters.Add(new SqlParameter("VPermission", permission));
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
            }
        }

        public DataTable DbViewingUsers()
        {
            using (var dtUsers = new DataTable())
            {
                try
                {
                    using (var command = new SqlCommand("SELECT * FROM Login", this.connection))
                    {
                        dtUsers.Load(command.ExecuteReader(CommandBehavior.Default));
                    }
                }
                catch(Exception ex)
                {
                    new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                    MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                    MessageUtils.LogUtils.WriteToLog(ex.Message);
                }
                return dtUsers;
            }
        }

        public void DeleteUserName(string id)
        {
            string query = String.Format("DELETE from Login WHERE id ={0};", id);
            try
            {
                using (var command = new SqlCommand(query, this.connection))
                {
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
            }
        }

        #endregion Manage Login And Users


        #region Manage Priority

        public DataTable DbViewingPriority()
        {
            using (var dtPriority = new DataTable())
            {
                try
                {
                    using (var command = new SqlCommand("SELECT * FROM Priorities", this.connection))
                    {
                        dtPriority.Load(command.ExecuteReader(CommandBehavior.Default));
                    }
                }
                catch(Exception ex)
                {
                    new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                    MessageUtils.LogUtils.SystemEventLogsError(ex.Message);
                    MessageUtils.LogUtils.WriteToLog(ex.Message);
                    return null;
                }
                return dtPriority;
            }
        }


        public bool DeletePriority(string id)
        {
            string query = String.Format("DELETE from Priorities WHERE idPriority={0};", id);
            try
            {
                using (var command = new SqlCommand(query, this.connection))
                {
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return false;
            }
            return true;
        }

        public bool DbUpdatePriority(string id, string priority)
        {
            try
            {
                string query = "UPDATE Priorities SET TypePriority=@TypePriority WHERE idPriority=" + id;
                using (var command = new SqlCommand(query, this.connection))
                {
                    command.Parameters.AddWithValue("@TypePriority", priority);
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return false;
            }
            return true;
        }

        public bool DbInsertPriority(string priority)
        {
            try
            {
                using (var command = new SqlCommand("INSERT INTO Priorities VALUES(@TypePriority)", this.connection))
                {
                    command.Parameters.Add(new SqlParameter("TypePriority", priority));
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsError(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return false;
            }
            return true;
        }

        #endregion Manage Priority


        #region Manage Calls Services

        public void DbInsertCallsServices(string idCustomers, string customersName,
            DateTime dateOpenCalls, string discriptions, string idPriority, string priority)
        {
            try
            {
                using (
                    var command =
                        new SqlCommand(
                            "INSERT INTO ServiceCalls VALUES(@idCompany, @dateOpenCalls, @discriptions, @idPriority)",
                            this.connection))
                {
                    command.Parameters.Add(new SqlParameter("idCompany", idCustomers));
                   // command.Parameters.Add(new SqlParameter("customerName", customersName));
                    command.Parameters.Add(new SqlParameter("dateOpenCalls", dateOpenCalls));
                    command.Parameters.Add(new SqlParameter("discriptions", discriptions));
                   // command.Parameters.Add(new SqlParameter("priority", priority));
                    command.Parameters.Add(new SqlParameter("idPriority", idPriority));
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return;
            }
        }

        public DataTable DbViewingServiceCalls()
        {
            using (var dtServiceCalls = new DataTable())
            {
                try
                {
                    string query = "SELECT C.companyName, SC.dateOpenCalls, SC.discriptions, P.TypePriority, SC.idCallsServices, SC.idCompany, SC.idPriority FROM ServiceCalls SC LEFT JOIN Companys C ON SC.idCompany = C.idCompany LEFT JOIN Priorities P ON SC.idPriority = P.idPriority ORDER BY C.companyName ";
                    using (var command = new SqlCommand(query, this.connection))
                    {
                        dtServiceCalls.Load(command.ExecuteReader(CommandBehavior.Default));
                    }
                }
                catch(Exception ex)
                {
                    new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                    MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                    MessageUtils.LogUtils.WriteToLog(ex.Message);
                    return null;
                }
                return dtServiceCalls;
            }
        }

        public void DeleteServiceCalls(string id)
        {
            string query = String.Format("DELETE from ServiceCalls WHERE idCallsServices={0};", id);
            try
            {
                using (var command = new SqlCommand(query, this.connection))
                {
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return;
            }
        }

        public void DbUpdateServiceCalls(string id, string discription)
        {
            try
            {
                string query = "UPDATE ServiceCalls SET discriptions=@discriptions WHERE idCallsServices=" + id;
                using (var command = new SqlCommand(query, this.connection))
                {
                    command.Parameters.AddWithValue("@discriptions", discription);
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return;
            }
        }

        public DataTable DbViewingServiceCallsByDate(string startDay, string endDay)
        {
            using (var dtServiceCallsByDate = new DataTable())
            {
                try
                {
                    string query = "SELECT C.companyName, SC.dateOpenCalls, SC.discriptions, P.TypePriority, SC.idCallsServices, SC.idCompany, SC.idPriority FROM ServiceCalls SC LEFT JOIN Companys C ON SC.idCompany = C.idCompany LEFT JOIN Priorities P ON SC.idPriority = P.idPriority WHERE dateOpenCalls BETWEEN @dateOpenCalls AND @EnddateOpenCalls ORDER BY C.companyName ";
                    //"SELECT * FROM ServiceCalls WHERE dateOpenCalls BETWEEN @dateOpenCalls AND @EnddateOpenCalls "
                    using (
                        var command =
                            new SqlCommand(query,this.connection))
                    {
                        command.Parameters.Add(new SqlParameter("dateOpenCalls", startDay));
                        command.Parameters.Add(new SqlParameter("EnddateOpenCalls", endDay));
                        dtServiceCallsByDate.Load(command.ExecuteReader(CommandBehavior.Default));
                    }
                }
                catch(Exception ex)
                {
                    new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                    MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                    MessageUtils.LogUtils.WriteToLog(ex.Message);
                    return null;
                }
                return dtServiceCallsByDate;
            }
        }

        #endregion Manage Calls Services


        #region Manage Project

        public void DbInsertProject(string projectName, string projectDiscriptions)
        {
            try
            {
                using (
                    var command = new SqlCommand("INSERT INTO project VALUES(@projectName,@projectDescription)",
                                                 this.connection))
                {
                    command.Parameters.Add(new SqlParameter("projectName", projectName));
                    command.Parameters.Add(new SqlParameter("projectDescription", projectDiscriptions));
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return;
            }
        }


        public void DbUpdateProject(string id, string projectName, string projectDiscriptions)
        {
            try
            {
                string query =
                    "UPDATE project SET projectName=@projectName,projectDescription=@projectDescription WHERE idProject=" +
                    id;
                using (var command = new SqlCommand(query, this.connection))
                {
                    command.Parameters.AddWithValue("@projectName", projectName);
                    command.Parameters.AddWithValue("@projectDescription", projectDiscriptions);
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return;
            }
        }


        public DataTable DbViewingProject()
        {
            using (var dtProject = new DataTable())
            {
                try
                {
                    using (var command = new SqlCommand("SELECT * FROM project", this.connection))
                    {
                        dtProject.Load(command.ExecuteReader(CommandBehavior.Default));
                    }
                }
                catch(Exception ex)
                {
                    new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                    MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                    MessageUtils.LogUtils.WriteToLog(ex.Message);
                    return null;
                }
                return dtProject;
            }
        }

        public void DeleteProjectRow(string id)
        {
            string query = String.Format("DELETE from project WHERE idProject={0};", id);
            try
            {
                using (var command = new SqlCommand(query, this.connection))
                {
                    command.ExecuteNonQuery();
                }
            }
            catch(Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return;
            }
        }

        #endregion Manage Project


        #region Manage Project To Company

        public DataTable DbViewingProjectToCompany()
        {
            using (var dtServices = new DataTable())
            {
                try
                {
                    const string query = "SELECT PC.idProject, PC.idCompany, C.companyName,P.projectName, PC.priceType, PC.discriptions,DP.DateStartProject,DP.DateFinishProject, PC.paid, PC.destination FROM ProjectCompany PC LEFT JOIN Companys C ON PC.idCompany = C.idCompany LEFT JOIN project P ON PC.idProject = P.idProject LEFT JOIN DateProjectCompany DP ON DP.idProject = PC.idProject AND DP.idCompany = PC.idCompany ORDER BY DP.DateStartProject";
                    using (var command = new SqlCommand(query, this.connection))
                    {
                        dtServices.Load(command.ExecuteReader(CommandBehavior.Default));
                    }
                }
                catch (Exception ex)
                {
                    new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                    MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                    MessageUtils.LogUtils.WriteToLog(ex.Message);
                }
                return dtServices;
            }
        }

        public DataTable DbViewingProjectToCompany(string idProject, string idCompany)
        {
            using (var dtServices = new DataTable())
            {
                try
                {
                    string query = string.Format("SELECT C.destination, D.DateStartProject, D.DateFinishProject FROM ProjectCompany C LEFT JOIN DateProjectCompany D ON C.idProject = D.idProject And C.idCompany = D.idCompany Where C.idCompany = {0} AND D.idProject = {1} ORDER BY D.DateStartProject", idCompany, idProject);
                    using (var command = new SqlCommand(query, this.connection))
                    {
                        dtServices.Load(command.ExecuteReader(CommandBehavior.Default));
                    }
                }
                catch (Exception ex)
                {
                    new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                    MessageUtils.LogUtils.SystemEventLogsError(ex.Message);
                    MessageUtils.LogUtils.WriteToLog(ex.Message);
                    return null;
                }
                return dtServices;
            }
        }

        public bool DbInsertManageProjectToCompany(string idProject, string idCompany,
            string priceType, string companyName, string projectName, string discriptions, string paid, DateTime dateStartProject, DateTime dateFinishProject, string destination)
        {
            string queryCompanyProject =
                "INSERT INTO ProjectCompany VALUES(@idProject,@idCompany,@priceType,@discriptions,@paid,@destination)";
            string queryDateCompanyProject =
                "INSERT INTO DateProjectCompany VALUES(@DateStartProject,@DateFinishProject,@idCompany,@idProject)";
            try
            {
                using (var command = new SqlCommand(queryCompanyProject, this.connection))
                {
                    command.Parameters.Add(new SqlParameter("idProject", idProject));
                    command.Parameters.Add(new SqlParameter("idCompany", idCompany));
                    command.Parameters.Add(new SqlParameter("priceType", priceType));
                    command.Parameters.Add(new SqlParameter("discriptions", discriptions));
                    command.Parameters.Add(new SqlParameter("paid", paid));
                    command.Parameters.Add(new SqlParameter("destination", destination));
                    command.ExecuteNonQuery();
                }
                using (var command = new SqlCommand(queryDateCompanyProject, this.connection))
                 {
                    command.Parameters.Add(new SqlParameter("DateStartProject", dateStartProject));
                    command.Parameters.Add(new SqlParameter("DateFinishProject", dateFinishProject));
                    command.Parameters.Add(new SqlParameter("idCompany", idCompany));
                    command.Parameters.Add(new SqlParameter("idProject", idProject));
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return false;
            }
            return true;
        }

        public bool DbUpdateProjectToCompany(string destination, string discriptions, string paid, string priceType, string customerId,
            string projectId, DateTime startProject, DateTime finishProject)
        {
            try
            {
                string queryCompanyProject =
                    String.Format(
                        "UPDATE ProjectCompany SET priceType=@priceType,discriptions=@discriptions,paid=@paid,destination=@destination WHERE idCompany={0}and idProject={1};",
                        customerId, projectId);

                string queryDateCompanyProject =
                    String.Format(
                        "UPDATE DateProjectCompany SET DateStartProject=@DateStartProject,DateFinishProject=@DateFinishProject WHERE idCompany={0}and idProject={1};",
                        customerId, projectId);

                using (var command = new SqlCommand(queryCompanyProject, this.connection))
                {
                    command.Parameters.AddWithValue("@priceType", priceType);
                    command.Parameters.AddWithValue("@discriptions", discriptions);
                    command.Parameters.AddWithValue("@paid", paid);
                    command.Parameters.AddWithValue("@destination", destination);
                    command.ExecuteNonQuery();

                    command.CommandText = queryDateCompanyProject;
                    command.Parameters.AddWithValue("@DateStartProject", startProject);
                    command.Parameters.AddWithValue("@DateFinishProject", finishProject);
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsError(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return false;
            }
            return true;
        }


        public bool DeleteRowProjectToCompany(string companyId, string projectId)
        {
            var queryCompanyProject = String.Format("DELETE from ProjectCompany WHERE idCompany={0}and idProject={1};",
                                                    companyId, projectId);
           var queryDateCompanyProject =
                String.Format("DELETE from DateProjectCompany WHERE idCompany={0}and idProject={1};", companyId,
                              projectId);
            try
            {
                using (var command = new SqlCommand(queryCompanyProject, this.connection))
                {
                    command.ExecuteNonQuery();

                    command.CommandText = queryDateCompanyProject;
                    command.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                MessageUtils.LogUtils.WriteToLog(ex.Message);
                return false;
            }
            return true;
        }

        #endregion Manage Project To Company

        public DataTable DbViewingRelationCompany(string id)
        {
            using (var dtCompany = new DataTable())
            {
                try
                {
                    // const string query = "SELECT CompanyProject.idProject, CompanyProject.idCompany,CompanyProject.companyName, CompanyProject.projectName ,CompanyProject.priceType  , CompanyProject.discriptions ,CompanyProject.paid, CompanyProject.destination , DateCompanyProject.DateStartProject, DateCompanyProject.DateFinishProject FROM CompanyProject INNER JOIN DateCompanyProject ON CompanyProject.idProject = DateCompanyProject.idProject and CompanyProject.idCompany= DateCompanyProject.idCompany ORDER BY DateCompanyProject.DateStartProject";
                    using (
                        var command =
                            new SqlCommand(
                                String.Format(
                                    "SELECT CompanyDetails.companyName Company_Name,CompanyDetails.contactName Contact_Name ,CompanyDetails.companynumber Company_Number,CompanyDetails.paymentTerms Payment_Terms, CompanyProject.projectName Project_Name,CompanyProject.paid Paid_Status  FROM CompanyDetails,CompanyProject WHERE CompanyDetails.idcust = {0} AND CompanyProject.idCompany ={0};",
                                    id), this.connection))
                    {
                        dtCompany.Load(command.ExecuteReader(CommandBehavior.Default));
                    }
                }
                catch(Exception ex)
                {
                    new MessageUtils.MessageBoxText(ex.Message, Titel).ErrorMessageBox();
                    MessageUtils.LogUtils.SystemEventLogsInformation(ex.Message);
                    MessageUtils.LogUtils.WriteToLog(ex.Message);
                }
                return dtCompany;
            }
        }
    }
}
